/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author copang
 */
public class DatabaseHandle {
    
    private Connection conn;
    private ResultSet rs; 
    private CallableStatement cstmt;
    private PreparedStatement pst;
    
    private boolean isNotExecuteQuery = true;
    
    public DatabaseHandle(String sql, boolean isNotExecuteQuery) {
        this.isNotExecuteQuery = isNotExecuteQuery;        
        try {
            
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost:1433;database=QuanLyNhaSach";
            conn = DriverManager.getConnection(url, "ad", "123");
            
            if (this.isNotExecuteQuery) {
                setSqlStoreProcedure(sql);                
            } else {
                setSqlQuery(sql);
            }            
        } catch (Exception ex) {
            Logger.getLogger(DatabaseHandle.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    protected void setSqlQuery(String sql) throws SQLException {
        pst = conn.prepareStatement(sql);
    }
    
    protected void setSqlStoreProcedure(String sql) throws SQLException {
        cstmt = conn.prepareCall(sql);
    }
    
    public void setParameter( int index, String para ) throws SQLException {
        if (!isNotExecuteQuery) {
            pst.setString(index, para);
        } else {
            cstmt.setString(index, para);
        }        
    }    
    public void setParameter( int index, int para ) throws SQLException {
        if (!isNotExecuteQuery) {
            pst.setInt(index, para);
        } else {
            cstmt.setInt(index, para);
        }
    }   
    public void setParameter( int index, boolean para ) throws SQLException {
        if (!isNotExecuteQuery) {
            pst.setBoolean(index, para);
        } else {
            cstmt.setBoolean(index, para);
        }
    }
    public void setParameter( int index, Date date ) throws SQLException {
        cstmt.setDate(index, (java.sql.Date) date);
    }
    
    public void setOutParameter( int index, int sqlType ) throws SQLException {
        cstmt.registerOutParameter(index, sqlType);
    }    
    public int getIntOutValue(int index) throws SQLException {
        return cstmt.getInt(index);
    }      
    public String getStringOutValue(int index) throws SQLException {
        return cstmt.getString(index);
    }
    
    /**
     * Execute SQL
     * @throws SQLException 
     */
    public boolean executeStorePro() throws SQLException {        
        cstmt.execute();
        return true;
    }
    
    public ResultSet excuteQuery() {
        try {
            rs = pst.executeQuery();
            return rs;
        } catch (SQLException e) {
            return null;
        }
    }
    
    /**
     * Close connection
     */
    public void closed(){
        try {                        
            if(cstmt != null &&!cstmt.isClosed()){
                cstmt.close();
            }
            if(conn != null && !conn.isClosed()){
                conn.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHandle.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}
